package com.matt.repository.impl;

import com.dotflat.util.UuidUtil;
import com.matt.foundation.DO.course.CourseTemplate2DO;
import com.matt.foundation.DO.course.WebCoursePageDO;
import com.matt.foundation.constant.CourseConstant;
import com.matt.foundation.query.CourseAreaQuery;
import com.matt.foundation.query.CourseQuery;
import com.matt.foundation.vo.course.CourseAreaBuildVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.CourseAllRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Objects;

/**
 * *******************************
 * Created by Ron.
 * form com.matt
 * on 2018/1/23
 * *******************************
 */
@Component
public class CourseAllRepositoryImpl extends BaseNativeSqlRepository implements CourseAllRepository {

    @Override
    public List<Object[]> findTypeNameListGroupByType(Integer type) {
        String findSql = String.format("select typeCode, typeName " +
                " from course WHERE deleted = false ");

        if (Objects.nonNull(type)) {
            findSql += String.format(" and type  = '%s'", type);
        }

        findSql += " group by typeCode,typeName order by typeCode ";
        List<Object[]> objectArrayList = sqlArrayList(findSql);
        return objectArrayList;
    }


    @Override
    public List<CourseTemplate2DO> findTemplate2ByTypeName(String typeName) {
        String findSql = String.format("select t.week as week, c.typeName as typeName, t.content as content " +
                " from course c, course_template_2 t WHERE c.courseTemplateId = t.id  and c.typeName = '%s' and c.deleted = false ", typeName);
        findSql += " order by t.week asc ";
        List<CourseTemplate2DO> objectArrayList = sqlObjectList(findSql, CourseTemplate2DO.class);
        return objectArrayList;
    }

    @Override
    public List<Object[]> findAreaAllType(String areaId, Integer type) {
        String findSql = String.format("select typeCode, typeName " +
                " from course WHERE deleted = false and areaId = '%s'", areaId);

        if (Objects.nonNull(type)) {
            findSql += String.format(" and type  = '%s'", type);
        }

        findSql += " group by typeCode,typeName order by typeCode ";
        List<Object[]> objectArrayList = sqlArrayList(findSql);
        return objectArrayList;
    }

    @Override
    public Long countCourseAreaByCondition(CourseAreaQuery query) {
        String querySql = String.format("select count(c.id) " +
                " from course c " +
                " WHERE c.type = '%s' and c.deleted = 'false' ", query.getType());

        if (StringUtils.isNotBlank(query.getAreaId())) {
            querySql += String.format(" and c.areaId='%s' ", query.getAreaId());
        }

        if (StringUtils.isNotBlank(query.getTypeName()))
            querySql += String.format(" and c.typeName='%s'", query.getTypeName());

        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (Objects.nonNull(query.getStatus())) {
            querySql += String.format(" and c.status  = '%s'", query.getStatus());
        }

        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<WebCoursePageDO> findCourseListByCondition(CourseQuery query) {
        String querySql = String.format("select c.id as id, c.typeName as typeName, c.name as name " +
                " from course c " +
                " WHERE true and c.deleted = 'false' and c.status=0 ");

        if (StringUtils.isNotBlank(query.getTypeName())) {
            querySql += String.format(" and c.typeName = '%s' ", query.getTypeName());
        }

        if (Objects.nonNull(query.getCourseType())) {
            querySql += String.format(" and c.type = '%s' ", query.getCourseType());
        }

        if (Objects.nonNull(query.getWeek())) {
            querySql += String.format(" and c.week = '%s' ", Integer.valueOf(query.getWeek()));
        }

        if (Objects.nonNull(query.getName())) {
            querySql += String.format(" and c.name  like  concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (StringUtils.isNotBlank(query.getAreaId())) {
            querySql += String.format(" and c.areaId = '%s' ", query.getAreaId());
        }

        querySql += " order by c.typeName asc,c.toplevel asc ";

        if (Objects.nonNull(query.getCourseType()) && CourseConstant.CourseType.SAT.codeOf() == query.getCourseType()) {
            querySql += " , c.week asc ";
        }
        querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        List<WebCoursePageDO> objectArrayList = sqlObjectList(querySql, WebCoursePageDO.class);
        return objectArrayList;
    }

    @Override
    public List<WebCoursePageDO> findCourseListByConditionV2(CourseQuery query,String coursePackageIds) {
        String querySql = String.format("select c.id as id, '' as typeName, c.title as name " +
                " from tc_vendor_course c  INNER JOIN tc_course_package cp  on cp.id = c.course_package_id " +
                " WHERE true and c.deleted = 'false' ");

        if (Objects.nonNull(coursePackageIds) && !StringUtils.equals(coursePackageIds,"")) {
            querySql += String.format(" and c.course_package_id  in  (%s) ", coursePackageIds);
        }else {
            List<WebCoursePageDO> objectArrayList =new ArrayList<>();
            return objectArrayList;
        }

        if (Objects.nonNull(query.getName())) {
            querySql += String.format(" and c.title  like  concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        querySql += " order by c.created desc ";

        querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        List<WebCoursePageDO> objectArrayList = sqlObjectList(querySql, WebCoursePageDO.class);
        return objectArrayList;
    }

    @Override
    public Long countCourseListByCondition(CourseQuery query) {
        String querySql = String.format("select count(id) " +
                " from course c " +
                " WHERE true and c.deleted = 'false' and c.status=0 ");

        if (StringUtils.isNotBlank(query.getTypeName())) {
            querySql += String.format(" and c.typeName = '%s' ", query.getTypeName());
        }

        if (Objects.nonNull(query.getCourseType())) {
            querySql += String.format(" and c.type = '%s' ", query.getCourseType());
        }

        if (Objects.nonNull(query.getWeek())) {
            querySql += String.format(" and c.week = '%s' ", Integer.valueOf(query.getWeek()));
        }

        if (Objects.nonNull(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (StringUtils.isNotBlank(query.getAreaId())) {
            querySql += String.format(" and c.areaId = '%s' ", query.getAreaId());
        }

        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public Long countCourseListByConditionV2(CourseQuery query,String coursePackageIds) {
        String querySql = String.format("select count(c.id)  " +
                "  from tc_vendor_course c  INNER JOIN tc_course_package cp  on cp.id = c.course_package_id  " +
                " WHERE true and c.deleted = 'false'   ");

        if (Objects.nonNull(coursePackageIds) && !StringUtils.equals(coursePackageIds,"")) {
            querySql += String.format(" and c.course_package_id  in  (%s) ", coursePackageIds);
        }else {
            return Long.valueOf(0);
        }

        if (Objects.nonNull(query.getName())) {
            querySql += String.format(" and c.title  like  concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        querySql += " order by c.created desc ";

        List objectArrayList = sqlSingleList(querySql);
        return Long.valueOf(objectArrayList.get(0).toString());
    }
    @Override
    public List<HashMap> findCourseCommonListByCondition(CourseAreaQuery query) {
        String querySql = String.format("SELECT c.id AS id, c.NAME AS name, c.typename AS typeName, c.lastmodifiedname AS modifyStaffName," +
                " c.lastmodified AS lastModified, c.lastModifiedId as modifiedId, c.status, d.beginMonth, d.endMonth, d.targetObjectName FROM course c " +
                "left JOIN course_detail d on c.id = d.courseId WHERE c.deleted = 'false' AND c.type = '%s'", query.getType());
        if (StringUtils.isNotBlank(query.getAreaId())) {
            querySql += String.format(" and c.areaId='%s' ", query.getAreaId());
        }
        if (StringUtils.isNotBlank(query.getTypeName()))
            querySql += String.format(" and c.typeName='%s'", query.getTypeName());
        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }
        if (Objects.nonNull(query.getStatus())) {
            querySql += String.format(" and c.status  = '%s'", query.getStatus());
        }
        querySql += " order by c.lastModified desc ";
        querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        List<HashMap> objectArrayList = sqlObjectList(querySql, HashMap.class);
        return objectArrayList;
    }

    @Override
    public List<Object[]> findCourseAreaBuildlListByCondition(CourseAreaQuery query) {
        String querySql = String.format("select c.id, c.name, c.typeName, c.lastModified, c.status, c.lastModifiedName as modifiedName, ct.beginMonth, ct.endMonth,c.created " +
                " from course c, course_template_3 ct  " +
                " WHERE c.courseTemplateId =ct.id " +
                "and c.deleted = 'false' and c.areaId = '%s' and c.type = '%s'", query.getAreaId(),CourseConstant.CourseType.AREA_BUILD.codeOf());


        if (StringUtils.isNotBlank(query.getTypeName())) {
            querySql += String.format(" and c.typeName = '%s' ", query.getTypeName());
        }

        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (Objects.nonNull(query.getStatus())) {
            querySql += String.format(" and c.status  = '%s'", query.getStatus());
        }

        querySql += " order by c.created desc ";
        querySql += String.format(" limit %s, %s ", query.getPageNum() * query.getPageSize(), query.getPageSize());
        List<Object[]> objectArrayList = sqlArrayList(querySql);
        return objectArrayList;
    }


    @Override
    public Long countCourseAreaBuildByCondition(CourseAreaQuery query) {
        String querySql = String.format("select count(c.id) " +
                " from course c, course_template_3 ct " +
                " WHERE c.courseTemplateId =ct.id and c.areaId = '%s' and c.type= '%s' and c.deleted = 'false' ",query.getAreaId(),CourseConstant.CourseType.AREA_BUILD.codeOf());

        if (StringUtils.isNotBlank(query.getTypeName())) {
            querySql += String.format(" and c.typeName = '%s' ", query.getTypeName());
        }

        if (StringUtils.isNotBlank(query.getName())) {
            querySql += String.format(" and c.name like concat('%s', '%s', '%s') ", "%", query.getName(), "%");
        }

        if (Objects.nonNull(query.getStatus())) {
            querySql += String.format(" and c.status  = '%s'", query.getStatus());
        }

        Long total = sqlSingleResult(querySql);
        return total;
    }

    @Override
    public List<CourseAreaBuildVo> getCourseAreaBuildByCourseId(String areaId, String courseId) {
        // TODO Auto-generated method stub
        String querySql = String.format("select c.id as courseId,c.type, c.typeId, c.typeName,ct.name, ct.status,ct.content,ct.attachments, " +
                " ct.beginMonth, ct.endMonth ,c.typeCode " +
                " from course c, course_template_3 ct  " +
                " WHERE c.courseTemplateId =ct.id " +
                "and c.deleted = 'false' and c.areaId='%s' and c.id = '%s'", areaId, courseId);
        List<CourseAreaBuildVo> objects = sqlObjectList(querySql, CourseAreaBuildVo.class);
        return objects;
    }

    @Override
    public List<Object[]> findKXThemeByAllType(Integer type) {
        String querySql = String.format("select ct.id,ct.name" +
                " from course_template_4 ct  " +
                " WHERE ct.type=5 ");
        List<Object[]> objectArrayList = sqlArrayList(querySql);
        return objectArrayList;
    }
}
